package com.ajk.server.dao.provider;

import java.text.MessageFormat;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import com.ajk.server.model.Disease;
import com.ajk.server.model.DiseaseReq;
import com.ajk.server.model.SymptomDisease;
import com.ajk.server.model.query.DiseaseQuery;
import com.ajk.server.model.request.SymptomReq;
import com.ajk.server.utils.IntegerUtil;
/**
 * 疾病库表  <br>
 * t_disease <br>
 * v1.0 2016-05-01
 * @see com.ajk.server.dao.DiseaseMapper
 */
 public class DiseaseProvider {
 
	 /**
	 	* 新增 
	 	*/
	 	public String insert(Disease domain){
			StringBuilder sb = new StringBuilder();
			
			sb.append("insert into t_disease(create_time,last_modify_time");
		 	if(domain.getName()!=null){
				sb.append(",name");
			}
		 	if(domain.getSystemName()!=null){
				sb.append(",system_name");
			}
		 	if(domain.getAgeSymp()!=null){
				sb.append(",age_symp");
			}
		 	if(domain.getSexSymp()!=null){
				sb.append(",sex_symp");
			}
		 	if(domain.getDiseaseDesc()!=null){
				sb.append(",disease_desc");
			}
		 	if(domain.getBookMsg()!=null){
				sb.append(",book_msg");
			}
		 	if(domain.getSymptom()!=null){
				sb.append(",symptom");
			}
		 	if(domain.getEndanger()!=null){
				sb.append(",endanger");
			}
		 	if(domain.getTreatment()!=null){
				sb.append(",treatment");
			}
		 	if(domain.getPrevent()!=null){
				sb.append(",prevent");
			}
		 	if(domain.getMedcineRec()!=null){
				sb.append(",medcine_rec");
			}
		 	if(domain.getKeywords()!=null){
				sb.append(",keywords");
			}
		 	if(domain.getOdds()!=null){
				sb.append(",odds");
			}
		 	if(domain.getInUsed()!=null){
				sb.append(",in_used");
			}
		 	if(domain.getDepartmentId()!=null){
				sb.append(",department_id");
			}
			
			sb.append(") values ( now(),now() ");
			
		 	if(domain.getName()!=null){
				sb.append(",#{name}");
			}
		 	if(domain.getSystemName()!=null){
				sb.append(",#{systemName}");
			}
		 	if(domain.getAgeSymp()!=null){
				sb.append(",#{ageSymp}");
			}
		 	if(domain.getSexSymp()!=null){
				sb.append(",#{sexSymp}");
			}
		 	if(domain.getDiseaseDesc()!=null){
				sb.append(",#{diseaseDesc}");
			}
		 	if(domain.getBookMsg()!=null){
				sb.append(",#{bookMsg}");
			}
		 	if(domain.getSymptom()!=null){
				sb.append(",#{symptom}");
			}
		 	if(domain.getEndanger()!=null){
				sb.append(",#{endanger}");
			}
		 	if(domain.getTreatment()!=null){
				sb.append(",#{treatment}");
			}
		 	if(domain.getPrevent()!=null){
				sb.append(",#{prevent}");
			}
		 	if(domain.getMedcineRec()!=null){
				sb.append(",#{medcineRec}");
			}
		 	if(domain.getKeywords()!=null){
				sb.append(",#{keywords}");
			}
		 	if(domain.getOdds()!=null){
				sb.append(",#{odds}");
			}
		 	if(domain.getInUsed()!=null){
				sb.append(",#{inUsed}");
			}
		 	if(domain.getDepartmentId()!=null){
				sb.append(",#{departmentId}");
			}
			sb.append(")");
			
			return sb.toString();
		}

		/**
	 	* 更新 
	 	*/
		public String update(Disease domain){
			StringBuilder sb = new StringBuilder();
			
			sb.append("update t_disease set last_modify_time = now() ");
			
			if(domain.getName()!=null){
				sb.append(",name=#{name}");
			}
			if(domain.getSystemName()!=null){
				sb.append(",system_name=#{systemName}");
			}
			if(domain.getAgeSymp()!=null){
				sb.append(",age_symp=#{ageSymp}");
			}
			if(domain.getSexSymp()!=null){
				sb.append(",sex_symp=#{sexSymp}");
			}
			if(domain.getDiseaseDesc()!=null){
				sb.append(",disease_desc=#{diseaseDesc}");
			}
			if(domain.getBookMsg()!=null){
				sb.append(",book_msg=#{bookMsg}");
			}
			if(domain.getSymptom()!=null){
				sb.append(",symptom=#{symptom}");
			}
			if(domain.getEndanger()!=null){
				sb.append(",endanger=#{endanger}");
			}
			if(domain.getTreatment()!=null){
				sb.append(",treatment=#{treatment}");
			}
			if(domain.getPrevent()!=null){
				sb.append(",prevent=#{prevent}");
			}
			if(domain.getMedcineRec()!=null){
				sb.append(",medcine_rec=#{medcineRec}");
			}
			if(domain.getKeywords()!=null){
				sb.append(",keywords=#{keywords}");
			}
			if(domain.getOdds()!=null){
				sb.append(",odds=#{odds}");
			}
			if(domain.getInUsed()!=null){
				sb.append(",in_used=#{inUsed}");
			}
			if(domain.getDepartmentId()!=null){
				sb.append(",department_id=#{departmentId}");
			}
			
			sb.append(" where id=#{id}");
			
			return sb.toString();
		}
		
		public String batchInsert(Map<String, List<Disease>> map){
			StringBuffer sb = new StringBuffer();
			List<Disease> diseases = map.get("list");
			sb.append("insert into t_disease(create_time,last_modify_time,name,system_name,age_symp,sex_symp,disease_desc,book_msg,symptom,endanger,treatment,prevent,medcine_rec,keywords,odds,in_used,department_id) values");
		    MessageFormat messageFormat = new MessageFormat("("
		    		+ "now(),now()"
		    		+ ",#'{'list[{0}].name}"
		    		+ ",#'{'list[{0}].systemName}"
		    		+ ",#'{'list[{0}].ageSymp}"
		    		+ ",#'{'list[{0}].sexSymp}"
		    		+ ",#'{'list[{0}].diseaseDesc}"
		    		+ ",#'{'list[{0}].bookMsg}"
		    		+ ",#'{'list[{0}].symptom}"
		    		+ ",#'{'list[{0}].endanger}"
		    		+ ",#'{'list[{0}].treatment}"
		    		+ ",#'{'list[{0}].prevent}"
		    		+ ",#'{'list[{0}].medcineRec}"
		    		+ ",#'{'list[{0}].keywords}"
		    		+ ",#'{'list[{0}].odds}"
		    		+ ",#'{'list[{0}].inUsed}"
		    		+ ",#'{'list[{0}].departmentId}"
		    		+")");
		    		
		    if(CollectionUtils.isNotEmpty(diseases)) {
				sb.append(messageFormat.format(new Integer[]{0}));
				for (int i = 1; i <  diseases.size(); i++) {
					sb.append(",").append(messageFormat.format(new Integer[]{i}));
				}
			}
			return sb.toString();
		}
	
	/**
 	*通过query 查询 疾病库表
 	*/
	public String findByQuery(DiseaseQuery query){
		StringBuffer sb = new StringBuffer();
		sb.append("select * from t_disease a where a.in_used = #{inUsed} ");
		if(StringUtils.isNotEmpty(query.getKeyword())){
			sb.append(" and (a.name like #{keyword} or a.system_name like #{keyword} or a.disease_desc like #{keyword})");
		}
		
//		if(IntegerUtil.isPositiveInteger(query.getBodypartId())){
//			sb.append(" and exists(select 1 from r_disease_bodypart where body_part_id = #{bodypartId} and in_used=1 and disease_id = a.id) ");
//		}
		
		if(IntegerUtil.isPositiveInteger(query.getStartId())){
			sb.append(" and a.id>#{startId} order by a.id limit #{limit}");
		}else if(IntegerUtil.isPositiveInteger(query.getEndId())){
			sb.append(" and a.id<#{endId} order by a.id desc limit #{limit}");
		}else if(query.getStart()!=null && query.getLimit()!=null && query.getLimit()!=0){
			sb.append(" order by a.id desc limit #{start},#{limit}");
		}else if(query.getLimit()!=null && query.getLimit()!=0){
			sb.append(" order by a.id desc limit #{limit}");
		}
		return sb.toString();
	}
	
	/**
 	*通过query 查询 疾病库表
 	*/
	public String findByQueryAdmin(DiseaseQuery query){
		StringBuffer sb = new StringBuffer();
		sb.append("select * from t_disease a where a.in_used = #{inUsed} ");
		if(StringUtils.isNotEmpty(query.getKeyword())){
			sb.append(" and (a.name like #{keyword} or a.system_name like #{keyword} or a.disease_desc like #{keyword})");
		}
		
		if(IntegerUtil.isPositiveInteger(query.getBodypartId())){
			sb.append(" and exists(select 1 from r_bodypart_symptom g,t_symptom f ,r_symptom_disease h,dict_bodypart i where g.symptom_id = f.id "
					+ "and f.in_used = #{inUsed} and f.id = h.symptom_id and i.id = g.bodypart_id and h.disease_id = a.id and i.id = #{bodypartId}) ");
		}
		
		if(IntegerUtil.isPositiveInteger(query.getStartId())){
			sb.append(" and a.id>#{startId} order by a.id limit #{limit}");
		}else if(IntegerUtil.isPositiveInteger(query.getEndId())){
			sb.append(" and a.id<#{endId} order by a.id desc limit #{limit}");
		}else if(query.getStart()!=null && query.getLimit()!=null && query.getLimit()!=0){
			sb.append(" order by a.id desc limit #{start},#{limit}");
		}else if(query.getLimit()!=null && query.getLimit()!=0){
			sb.append(" order by a.id desc limit #{limit}");
		}
		return sb.toString();
	}
	
	public String countByQuery(DiseaseQuery query){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(1) from t_disease a where a.in_used = #{inUsed} ");
		if(StringUtils.isNotEmpty(query.getKeyword())){
			sb.append(" and (a.name like #{keyword} or a.system_name like #{keyword} or a.disease_desc like #{keyword})");
		}
		
		if(IntegerUtil.isPositiveInteger(query.getBodypartId())){
			sb.append(" and exists(select 1 from r_disease_bodypart where body_part_id = #{bodypartId} and in_used=1 and disease_id = a.id) ");
		}
		return sb.toString();
	}
	
	/**
 	*通过ids 查询 疾病库表
 	*/
 	public String findByIds(Map<String, List<Integer>> map){
		StringBuffer sb = new StringBuffer();
		List<Integer> ids = map.get("list");
		sb.append("select * from t_disease where ");
		if(ids==null || ids.isEmpty()){
			sb.append(" 1<2");
		}else{
			if(ids.size()==1){
				sb.append(" id=").append("#{list[0]}");
			}else{
				sb.append(" id in (").append("#{list[0]}");
				for(int i=1; i<ids.size() ; i++){
					sb.append(",").append("#{list[").append(i).append("]}");
				}
				sb.append(")");
			}
		}
		return sb.toString();
	}
 	
 	public String symptomList(SymptomReq symptomReq){
 		StringBuffer sb = new StringBuffer();
 		sb.append("select * from (select a.id,max(a.name) name,group_concat(c.name) bodypart_names from t_symptom a,r_bodypart_symptom b,dict_bodypart c  ");
 		sb.append("where a.id = b.symptom_id and b.bodypart_id = c.id ");
 		if(symptomReq.getKeyword() != null && !symptomReq.getKeyword().trim().isEmpty()){
 			sb.append(" and a.name like concat('%',#{keyword},'%')");
 		}
 		if(symptomReq.getBodypartId() != null && symptomReq.getBodypartId() != 0){
 			sb.append(" and b.bodypart_id = #{bodypartId}");
 		}
 		sb.append(" group by a.id")	;
 		sb.append(" limit #{start},#{limit} ) b order by b.id desc");
 		return sb.toString();
 	}
 	
 	public String symptomListCount(SymptomReq symptomReq){
 		StringBuffer sb = new StringBuffer();
 		sb.append("select count(1) cnt from (select a.id,max(a.name) name,group_concat(c.name) bodypart_names from t_symptom a,r_bodypart_symptom b,dict_bodypart c  ");
 		sb.append("where a.id = b.symptom_id and b.bodypart_id = c.id ");
 		if(symptomReq.getKeyword() != null && !symptomReq.getKeyword().trim().isEmpty()){
 			sb.append(" and a.name like concat('%',#{keyword},'%')");
 		}
 		if(symptomReq.getBodypartId() != null && symptomReq.getBodypartId() != 0){
 			sb.append(" and b.bodypart_id = #{bodypartId}");
 		}
 		sb.append(" group by a.id ) b ")	;
 		return sb.toString();
 	}
 	
 	public String addBodypartSymptom(SymptomReq symptomReq){
 		StringBuffer sb = new StringBuffer();
		List<Integer> ids = symptomReq.getBodypartIds();
		sb.append("insert into r_bodypart_symptom(bodypart_id,symptom_id) values ");
	    MessageFormat messageFormat = new MessageFormat("("
	    		+ "#'{'bodypartIds[{0}]},"
	    		+ "#'{'id}"
	    		+")");
	    		
	    if(CollectionUtils.isNotEmpty(ids)) {
			sb.append(messageFormat.format(new Integer[]{0}));
			for (int i = 1; i <  ids.size(); i++) {
				sb.append(",").append(messageFormat.format(new Integer[]{i}));
			}
		}
		return sb.toString();
 	}
 	
 	public String symptomListForNmae(SymptomReq symptomReq){
 		StringBuffer sb = new StringBuffer();
 		sb.append("select * from t_symptom where name =#{name}");
 		if(symptomReq.getId() != null && symptomReq.getId() != 0){
 			sb.append(" and id != #{id}");
 		}
 		return  sb.toString();
 	}
 	
 	public String insertSymptomDisease(DiseaseReq domain){
 		StringBuffer sb = new StringBuffer();
 		List<SymptomDisease> symptomDiseases = domain.getSymptomDiseases();
		sb.append("insert into r_symptom_disease (symptom_id,disease_id,matching_rate) values ");
	    MessageFormat messageFormat = new MessageFormat("("
	    		+ "#'{'symptomDiseases[{0}].symptomId},"
	    		+ "#'{'id},"
	    		+ "#'{'symptomDiseases[{0}].matchingRate}"
	    		+")");
	    		
	    if(CollectionUtils.isNotEmpty(symptomDiseases)) {
			sb.append(messageFormat.format(new Integer[]{0}));
			for (int i = 1; i <  symptomDiseases.size(); i++) {
				sb.append(",").append(messageFormat.format(new Integer[]{i}));
			}
		}
		return sb.toString();
 	}
	
 }	